package com.wcfsy.wechat.wx.util.excel;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.mail.internet.MimeUtility;

import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WriteException;

import org.apache.commons.lang.ArrayUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class ExcelUtil {

	private static String sheetName = "data";

	private HSSFWorkbook workBook;

	private HSSFSheet sheet;

	private HSSFRow row;

	private HSSFCell cell;

	private HSSFCellStyle cellStyle;

	private HSSFCellStyle headCellStyle;

	public ExcelUtil(HSSFWorkbook workBook) {
		this.workBook = workBook;
	}

	/**
	 * 生成excel的workBook
	 * 
	 * @param list
	 *            数据源
	 * @param headerMap
	 *            excel 头部
	 */

	public HSSFWorkbook outputWorkBook(List<?> list,
			Map<String, String> headerMap) {

		createHeadRow(headerMap);
		createDateRow(list, headerMap);

		return workBook;
	}

	public static WritableCellFormat getHeaderCellStyle() {
		/*
		 * WritableFont.createFont("宋体")：设置字体为宋体 10：设置字体大小
		 * WritableFont.BOLD:设置字体加粗（BOLD：加粗 NO_BOLD：不加粗） false：设置非斜体
		 * UnderlineStyle.NO_UNDERLINE：没有下划线
		 */
		WritableFont font = new WritableFont(WritableFont.createFont("微软雅黑"),
				11, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE);

		WritableCellFormat headerFormat = new WritableCellFormat(
				NumberFormats.TEXT);
		try {
			// 添加字体设置
			headerFormat.setFont(font);
			// 设置单元格背景色：表头为黑色
			headerFormat.setBackground(Colour.GRAY_25);
			// 设置表头表格边框样式
			// 整个表格线为粗线、黑色
			// headerFormat.setBorder(Border.ALL, BorderLineStyle.THICK,
			// Colour.BLACK);
			// 表头内容水平居中显示
			headerFormat.setAlignment(Alignment.CENTRE);
		} catch (WriteException e) {
			e.printStackTrace();
		}
		return headerFormat;
	}

	/**
	 * 创建excel 头部
	 * 
	 * 
	 */
	private void createHeadRow(Map<String, String> headerMap) {
		try {
			sheet = workBook.createSheet(ExcelUtil.sheetName);
			sheet.setDefaultColumnWidth(20);
			headCellStyle = workBook.createCellStyle();
			headCellStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
			headCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			headCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			headCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			headCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			headCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			headCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			HSSFFont font = workBook.createFont();
			font.setColor(HSSFColor.VIOLET.index);
			font.setFontHeightInPoints((short) 12);
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			headCellStyle.setFont(font);
			cellStyle = workBook.createCellStyle();
			cellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
			cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

			HSSFFont font2 = workBook.createFont();
			font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
			cellStyle.setFont(font2);
			row = sheet.createRow(0);
			Iterator<String> iterator = headerMap.values().iterator();
			for (int i = 0; iterator.hasNext(); i++) {
				cell = row.createCell(i);
				cell.setCellStyle(headCellStyle);
				// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
				cell.setCellValue((String) iterator.next());
			}
		} catch (Exception ex) {
			System.out.print(ex);
		}
	}

	/**
	 * 循环list每个数据，每循环一次生成一行表格与list每个元素对应。 循环完成，生成整个表格内容与list对应
	 * 
	 * @param list
	 *            data list
	 * @param headerMap
	 *            table header return
	 * @return data stream,byte[]
	 */

	private void createDateRow(List<?> list, Map<String, String> headerMap) {

		if (list != null) {
			for (int i = 0; i < list.size(); i++) {
				row = sheet.createRow(i + 1);
				Object obj = list.get(i);
				this.createCell(row, obj, headerMap);
			}
		}

	}

	/**
	 * 循环obj每个属性，每循环一次生成一个单元格与obj每个属性对应。 循环完成，生成一行表格与obj对应
	 */

	private void createCell(HSSFRow row, Object obj,
			Map<String, String> headerMap) {

		Class<? extends Object> classz = obj.getClass();
		Field[] fields = classz.getDeclaredFields();
		// support super class,just fit with BaseDto
		if (obj.getClass().getSuperclass() != null) {
			Field[] superFields = obj.getClass().getSuperclass()
					.getDeclaredFields();
			fields = (Field[]) ArrayUtils.addAll(fields, superFields);
		}
		try {
			Iterator<String> iterator = headerMap.keySet().iterator();
			for (int i = 0; iterator.hasNext(); i++) {
				cell = row.createCell(i);
				cell.setCellStyle(cellStyle);

				String propertieValue = iterator.next();
				// cell.setEncoding(HSSFCell.ENCODING_UTF_16);

				Object result = null;
				for (int j = 0; j < fields.length; j++) {
					Field field = fields[j];
					String fieldName = field.getName();
					// headerMap's key maybe for example a.b,a is object b is
					// a's properties
					if (propertieValue.contains(".")) {
						if (fieldName.equals(propertieValue.substring(0,
								propertieValue.indexOf(".")))) {
							Method method = classz.getMethod(
									getMethodName(field), new Class[] {});
							Object tempObjct = method.invoke(obj,
									new Object[] {});
							if (tempObjct != null) {
								try {
									Class<? extends Object> tempClassz = tempObjct
											.getClass();
									Field tempField = tempClassz
											.getDeclaredField(propertieValue
													.substring(propertieValue
															.indexOf(".") + 1));
									Method tempMethod = tempClassz.getMethod(
											getMethodName(tempField),
											new Class[] {});
									result = tempMethod.invoke(tempObjct,
											new Object[] {});
								} catch (Exception e) {
									Class<? extends Object> tempClassz = tempObjct
											.getClass().getSuperclass();
									if (tempClassz != null) {
										Field tempField = tempClassz
												.getDeclaredField(propertieValue
														.substring(propertieValue
																.indexOf(".") + 1));
										Method tempMethod = tempClassz
												.getMethod(
														getMethodName(tempField),
														new Class[] {});
										result = tempMethod.invoke(tempObjct,
												new Object[] {});
									}
								}
							}
							break;
						}
					} else {
						if (fieldName.equals(propertieValue)) {
							Method method = classz.getMethod(
									getMethodName(field), new Class[] {});
							result = method.invoke(obj, new Object[] {});
							break;
						}
					}
				}
				String textValue = null;
				if (result != null) {
					if (result instanceof Boolean) {
						boolean bValue = (Boolean) result;
						textValue = "Y";
						if (!bValue) {
							textValue = "N";
						}
					} else if (result instanceof Date) {
						Date date = (Date) result;
						SimpleDateFormat sdf = new SimpleDateFormat(
								"yyyy-MM-dd");
						textValue = sdf.format(date);
					} else if (result instanceof Double) {
						DecimalFormat df = new DecimalFormat("#.##");

						textValue = df.format(result);
					} else {
						// other
						textValue = result.toString();
					}
				}
				cell.setCellValue(textValue);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	private String getMethodName(Field field) {
		String getMethodName = "";
		if (field.getType().getName().equals("boolean")) {
			getMethodName = "is"
					+ field.getName().substring(0, 1).toUpperCase()
					+ field.getName().substring(1);
		} else {
			getMethodName = "get"
					+ field.getName().substring(0, 1).toUpperCase()
					+ field.getName().substring(1);
		}
		return getMethodName;
	}

	/**
	 * 处理中文文件名
	 * 
	 * @param filename
	 * @param request
	 * @return
	 */
	public static String encodeFilename(String filename, String agent) {
		/*
		 * 获取客户端浏览器和操作系统信息 在IE浏览器中得到的是：User-Agent=Mozilla/4.0 (compatible; MSIE
		 * 6.0; Windows NT 5.1; SV1; Maxthon; Alexa Toolbar)
		 * 在Firefox中得到的是：User-Agent=Mozilla/5.0 (Windows; U; Windows NT 5.1;
		 * zh-CN; rv:1.7.10) Gecko/20050717 Chrome浏览器 :User-Agent=Mozilla/5.0
		 * (Windows NT 5.1) AppleWebKit/537.36 (KHTML, like Gecko)
		 * Chrome/28.0.1500.95 Safari/537.36
		 */
		String newFileName = "";
		try {
			if (agent.indexOf("MSIE") != -1) {// IE
				newFileName = URLEncoder.encode(filename, "UTF-8");
				newFileName = StringUtils.replace(newFileName, "+", "%20");
				if (newFileName.length() > 150) {
					newFileName = new String(filename.getBytes("GB2312"),
							"ISO8859-1");
					newFileName = StringUtils.replace(newFileName, " ", "%20");
				}
			} else if (agent.indexOf("Chrome") != -1) {// Chrome
				newFileName = URLEncoder.encode(filename, "UTF-8");
			} else {// FireFox
				newFileName = MimeUtility.encodeText(filename, "UTF-8", "B");
			}
		} catch (Exception ex) {
			newFileName = filename;
		}
		return newFileName;
	}

}
